import { Database } from 'bun:sqlite';
import { drizzle } from 'drizzle-orm/bun-sqlite';
import { migrate } from 'drizzle-orm/bun-sqlite/migrator';
import * as schema from '../db/schema';

// 创建数据库连接
const sqlite = new Database('./database.sqlite');

// 启用外键约束
sqlite.exec('PRAGMA foreign_keys = ON');

// 创建 Drizzle 实例
const db = drizzle(sqlite, { schema });

console.log('🗄️ 开始初始化数据库...');

try {
  // 手动创建表结构
  console.log('🔄 创建用户表...');
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      username TEXT NOT NULL UNIQUE,
      email TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('admin', 'user', 'guest')),
      avatar TEXT,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned')),
      last_login_at TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  console.log('🔄 创建产品分类表...');
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS product_categories (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT,
      image TEXT,
      sort INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  console.log('🔄 创建产品表...');
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS products (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      type TEXT NOT NULL,
      category_id TEXT,
      description TEXT,
      content TEXT,
      price_range TEXT,
      images TEXT,
      features TEXT,
      tags TEXT,
      craft_types TEXT,
      specifications TEXT,
      sort INTEGER NOT NULL DEFAULT 0,
      featured INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'draft')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL
    );
  `);

  console.log('🔄 创建案例分类表...');
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS case_categories (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT,
      image TEXT,
      sort INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  console.log('🔄 创建案例表...');
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS cases (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      category_id TEXT,
      industry TEXT,
      description TEXT,
      content TEXT,
      duration TEXT,
      services TEXT,
      images TEXT,
      highlights TEXT,
      technologies TEXT,
      tags TEXT,
      client_name TEXT,
      completed_at TEXT,
      location TEXT,
      project_value TEXT,
      challenges TEXT,
      solutions TEXT,
      featured INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'draft')),
      sort INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (category_id) REFERENCES case_categories(id) ON DELETE SET NULL
    );
  `);

  console.log('🔄 创建标签表...');
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS tags (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL UNIQUE,
      color TEXT,
      description TEXT,
      usage_count INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  console.log('🔄 创建其他表...');
  sqlite.exec(`
    CREATE TABLE IF NOT EXISTS settings (
      id TEXT PRIMARY KEY,
      key TEXT NOT NULL UNIQUE,
      value TEXT,
      type TEXT NOT NULL DEFAULT 'string' CHECK (type IN ('string', 'number', 'boolean', 'json')),
      description TEXT,
      category TEXT NOT NULL DEFAULT 'general',
      is_public INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS uploads (
      id TEXT PRIMARY KEY,
      filename TEXT NOT NULL,
      original_name TEXT NOT NULL,
      mime_type TEXT NOT NULL,
      size INTEGER NOT NULL,
      path TEXT NOT NULL,
      url TEXT NOT NULL,
      uploaded_by TEXT,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'deleted')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
    );

    CREATE TABLE IF NOT EXISTS contacts (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      position TEXT,
      description TEXT,
      phone TEXT,
      email TEXT,
      wechat TEXT,
      qq TEXT,
      avatar TEXT,
      expertise TEXT,
      work_hours TEXT,
      sort INTEGER NOT NULL DEFAULT 0,
      featured INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS banners (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      subtitle TEXT,
      image TEXT NOT NULL,
      link TEXT,
      target TEXT NOT NULL DEFAULT '_self' CHECK (target IN ('_self', '_blank')),
      sort INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
      start_date TEXT,
      end_date TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS system_logs (
      id TEXT PRIMARY KEY,
      level TEXT NOT NULL CHECK (level IN ('info', 'warn', 'error', 'debug')),
      module TEXT NOT NULL,
      action TEXT NOT NULL,
      message TEXT NOT NULL,
      user_id TEXT,
      username TEXT,
      ip TEXT,
      user_agent TEXT,
      request_id TEXT,
      metadata TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    );

    CREATE TABLE IF NOT EXISTS backups (
      id TEXT PRIMARY KEY,
      filename TEXT NOT NULL,
      file_path TEXT NOT NULL,
      file_size INTEGER NOT NULL,
      backup_type TEXT NOT NULL CHECK (backup_type IN ('manual', 'auto', 'scheduled')),
      status TEXT NOT NULL DEFAULT 'completed' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
      created_by TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
    );
  `);

  console.log('🔄 创建索引...');
  const indexes = [
    'CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)',
    'CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)',
    'CREATE INDEX IF NOT EXISTS idx_users_status ON users(status)',
    'CREATE INDEX IF NOT EXISTS idx_product_categories_status ON product_categories(status)',
    'CREATE INDEX IF NOT EXISTS idx_product_categories_sort ON product_categories(sort)',
    'CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id)',
    'CREATE INDEX IF NOT EXISTS idx_products_status ON products(status)',
    'CREATE INDEX IF NOT EXISTS idx_products_featured ON products(featured)',
    'CREATE INDEX IF NOT EXISTS idx_products_sort ON products(sort)',
    'CREATE INDEX IF NOT EXISTS idx_case_categories_status ON case_categories(status)',
    'CREATE INDEX IF NOT EXISTS idx_case_categories_sort ON case_categories(sort)',
    'CREATE INDEX IF NOT EXISTS idx_cases_category_id ON cases(category_id)',
    'CREATE INDEX IF NOT EXISTS idx_cases_status ON cases(status)',
    'CREATE INDEX IF NOT EXISTS idx_cases_featured ON cases(featured)',
    'CREATE INDEX IF NOT EXISTS idx_cases_sort ON cases(sort)',
    'CREATE INDEX IF NOT EXISTS idx_contacts_status ON contacts(status)',
    'CREATE INDEX IF NOT EXISTS idx_contacts_featured ON contacts(featured)',
    'CREATE INDEX IF NOT EXISTS idx_contacts_sort ON contacts(sort)',
    'CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(key)',
    'CREATE INDEX IF NOT EXISTS idx_settings_category ON settings(category)',
    'CREATE INDEX IF NOT EXISTS idx_uploads_uploaded_by ON uploads(uploaded_by)',
    'CREATE INDEX IF NOT EXISTS idx_uploads_status ON uploads(status)',
    'CREATE INDEX IF NOT EXISTS idx_banners_status ON banners(status)',
    'CREATE INDEX IF NOT EXISTS idx_banners_sort ON banners(sort)',
    'CREATE INDEX IF NOT EXISTS idx_system_logs_level ON system_logs(level)',
    'CREATE INDEX IF NOT EXISTS idx_system_logs_module ON system_logs(module)',
    'CREATE INDEX IF NOT EXISTS idx_system_logs_user_id ON system_logs(user_id)',
    'CREATE INDEX IF NOT EXISTS idx_system_logs_created_at ON system_logs(created_at)',
    'CREATE INDEX IF NOT EXISTS idx_backups_status ON backups(status)',
    'CREATE INDEX IF NOT EXISTS idx_backups_created_by ON backups(created_by)'
  ];
  
  for (let i = 0; i < indexes.length; i++) {
    try {
      sqlite.exec(indexes[i]);
      console.log(`✅ 索引 ${i + 1}/${indexes.length} 创建成功`);
    } catch (error) {
      console.error(`❌ 索引 ${i + 1} 创建失败:`, indexes[i]);
      console.error('错误:', error);
    }
  }
  
  console.log('✅ 数据库初始化完成！');
} catch (error) {
  console.error('❌ 数据库初始化失败:', error);
  process.exit(1);
} finally {
  sqlite.close();
}